﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.OleDb;


namespace Home.Utils
{

    public class ParameterSetter
    {

        List<Parameter> paramList = new List<Parameter>();
        Dictionary<string, object> paramValueList = new Dictionary<string, object>();
       // string tableName;
        #region fields
        string commandString;
        #endregion

        #region construtor

        #endregion
        #region properties
        /// <summary>
        /// User to set parameters value in a command string
        /// </summary>
        public ParameterSetter()
        {
            this.CommandString = "";
        }
        public ParameterSetter(string commandString)
        {
            this.CommandString = commandString;
        }
        /// <summary>
        /// Get or set command string
        /// </summary>
        public string CommandString
        {
            get { return this.commandString; }
            set { commandString = value; }
        }
        #endregion
        private bool IsValidCommand()
        {
            if (Regex.IsMatch(CommandString, "[^,]+[@]([,])?")) return false;
            return true;
        }
        /// <summary>
        /// Add condition for WHERE clause (not include 'Where' keyword)
        /// </summary>
        /// <param name="param"></param>
        public void AddCondition(Parameter param)
        {
            this.paramList.Add(param);
        }
        public void SetParameter(string paramName, object value)
        {
            paramValueList.Add(paramName, value);
        }

        public string GetWhereClause()
        {
            string strCommand = this.CommandString + " 1=1 ";
            try
            {
                string value = "";
                string typeName = "";
                bool isEqual = false;
                foreach (Parameter pr in paramList)
                {
                    typeName = pr.Value.GetType().Name;
                    CompareData(typeName, pr.Value, pr.ExcludedValue, ref isEqual, ref value);

                    if (!isEqual) strCommand += ((pr.AndCondition ? " AND " : " OR ") + pr.ColumnName + " " + pr.Operator + " " + value);
                }
            }
            catch (Exception ex) { throw ex; }
            return strCommand;
        }
        /// <summary>
        /// Return command string with parameters replaced by values
        /// </summary>
        /// <returns></returns>
        public string GetBuildedCommandString()
        {
            string strCommand = this.CommandString;
            Dictionary<string, object>.KeyCollection keyset = paramValueList.Keys;
            try
            {
                string currentKey = "";
                string value = "";
                string typeName = "";
                object obj = null;
                string[] keys=new string[keyset.Count];
                for (int i = 0; i < keys.Length; i++)
                {
                    currentKey = keys[i];//.ElementAt(i).ToString();
                    paramValueList.TryGetValue(currentKey, out obj);
                    typeName = obj.GetType().Name;

                    switch (typeName)
                    {
                        case "String": value = "'" + obj.ToString() + "'"; break;
                        case "Int32": value = obj.ToString(); break;
                        case "Int16": value = obj.ToString(); break;
                        case "Int64": value = obj.ToString(); break;
                        case "Decimal": value = obj.ToString(); break;
                        case "Double": value = obj.ToString(); break;
                        case "Float": value = obj.ToString(); break;
                        case "Boolean": value = (bool)obj == true ? "1" : "0"; break;
                        case "DateTime": value = "'" + ((DateTime)obj).ToString("MM/dd/yyyy hh:mm:ss") + "'"; break;
                    }
                    strCommand = Regex.Replace(strCommand, "[^']" + currentKey + "\\s*,", " = " + value + ", ", RegexOptions.IgnoreCase);
                }
            }
            catch (Exception ex) { throw ex; }
            return strCommand;
        }


        private void CompareData(string dataTypeName, object value1, object value2, ref bool isEqual, ref string sqlStringValue)
        {
            switch (dataTypeName)
            {
                case "String": sqlStringValue = "'" + value1.ToString() + "'"; isEqual = value1.ToString() == value2.ToString(); break;
                case "Int32": sqlStringValue = value1.ToString(); isEqual = (int)value1 == (int)value2; break;
                case "Int16": sqlStringValue = value1.ToString(); isEqual = (Int16)value1 == (Int16)value2; break;
                case "Int64": sqlStringValue = value1.ToString(); isEqual = (Int64)value1 == (Int64)value2; ; break;
                case "Decimal": sqlStringValue = value1.ToString(); isEqual = (decimal)value1 == (decimal)value2; break;
                case "Double": sqlStringValue = value1.ToString(); isEqual = (double)value1 == (double)value2; break;
                case "Float": sqlStringValue = value1.ToString(); isEqual = (float)value1 == (float)value2; break;
                case "Boolean": sqlStringValue = (bool)value1 == true ? "1" : "0"; break;
                case "DateTime": sqlStringValue = "'" + ((DateTime)value1).ToString("MM/dd/yyyy") + "'"; isEqual = (DateTime)value1 == (DateTime)value2; break;

                default: sqlStringValue = ""; isEqual = true; break;
            }
        }

    }

    public class Parameter
    {
        string columnName;
        string opr;
        //string paramName;
        object value;
        object excludeValue;
        bool visible;
        bool andCondition;
        string identifier;
        /// <summary>
        /// Name of column
        /// </summary>
        public string ColumnName { get { return columnName; } set { columnName = value; } }
        /// <summary>
        /// Comparation operator
        /// </summary>
        public string Operator { get { return opr; } set { opr = value; } }
        /// <summary>
        /// Get name of parameter according to specified columns ('@' + [ColumnName])
        /// </summary>
        public string ParamName { get { return ParamName.Trim() == "" ? "@" + ColumnName.Trim() : "@" + Identifier.Trim(); } }
        /// <summary>
        /// Value to compare
        /// </summary>
        public object Value { get { return this.value; } set { this.value = value; } }
        /// <summary>
        /// Value to be excluded. If comparation value equal excluded value then the condition will not include current column
        /// </summary>
        public object ExcludedValue { get { return excludeValue; } set { excludeValue = value; } }
        /// <summary>
        /// Allow current column appear in condition or not
        /// </summary>
        public bool Visible { get { return visible; } set { visible = value; } }
        /// <summary>
        /// Specify operator to join current condition with before conditions is either 'AND' or 'OR' (true:'AND')
        /// </summary>
        public bool AndCondition { get { return andCondition; } set { andCondition = value; } }

        public string Identifier { get { return identifier; } set { identifier = value; } }
        /// <summary>
        /// Alternating name for parameter. It must be specified when the column appear more than one time in sql statement
        /// </summary>       
        /// <summary>
        /// 
        /// </summary>
        /// <param name="column">Column name</param>
        /// <param name="opr">Comparation operator</param>
        /// <param name="value">Value to compare</param>
        /// <param name="excludedValue">Excluded value</param>
        /// <param name="andCondition">Using either 'AND' or 'OR' operator to join condition</param>
        public Parameter(string column, string opr, object value, object excludedValue, bool andCondition, string indentifier)
        {
            this.ColumnName = column;
            this.Operator = opr;
            this.Value = value;
            this.ExcludedValue = excludedValue;
            this.AndCondition = andCondition;
            this.Visible = true;
        }
    }

    public class CommandBuilder
    {
        List<Parameter> paramList = new List<Parameter>();
        string strCommand = "";
        public string BuildedCommandString { get { return strCommand; } }
        public CommandBuilder(string commandString)
        {
            this.strCommand = commandString;
        }
        public void AddCondition(Parameter param)
        {
            paramList.Add(param);
        }
        /// <summary>
        /// Return a command string joined with condition
        /// </summary>
        /// <param name="conn">Connection object</param>
        /// <param name="commandString">SQL command string without condition</param>
        /// <returns>SqlCommand object with passed parameters</returns>
        public OleDbCommand GetBuildedCommand(OleDbConnection conn, string commandString)
        {
            if (paramList.Count == 0) return new OleDbCommand(commandString, conn);
            OleDbCommand command;
            strCommand = commandString;
            strCommand += " WHERE 1=1";
            foreach (Parameter pr in paramList)
            {
                if (pr.Value == pr.ExcludedValue) pr.Visible = false;
                else
                {
                    strCommand += (pr.AndCondition ? " AND " : " OR " + pr.ColumnName + " " + pr.Operator + " " + pr.ParamName);
                }
            }
            command = new OleDbCommand(strCommand, conn);
            foreach (Parameter pr in paramList)
            {
                if (pr.Visible)
                {
                    command.Parameters.AddWithValue(pr.ParamName, pr.Value);
                }
            }
            return command;
        }
    }

    public class NonQueryCommandBuilder
    {
        string tableName;
        Dictionary<string, object> valueFields = new Dictionary<string, object>();
        Dictionary<string, object> conditionFields = new Dictionary<string, object>();
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">Ten bang se ap dung cau lenh</param>
        public NonQueryCommandBuilder(string table)
        {
            this.tableName = table;
        }

        /// <summary>
        /// Them tham so truoc mend de WHERE
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void AddValueField(string name, object value)
        {
            valueFields.Add(name, value);

        }

        /// <summary>
        /// Them tham so sau menh de WHERE
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void AddConditionField(string name, object value)
        {
            conditionFields.Add(name, value);
        }

        /// <summary>
        /// Tra ve cau lenh UPDATE voi tham so duoc truyen
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="andCondition"></param>
        /// <returns></returns>
        public OleDbCommand GetUpdateCommand(OleDbConnection conn, bool andCondition)
        {
            
            string[] keys =new string[valueFields.Keys.Count];
            valueFields.Keys.CopyTo(keys, 0);
            string[] ckeys = new string[conditionFields.Keys.Count];
            conditionFields.Keys.CopyTo(ckeys, 0);
            string condition = andCondition ? " AND " : " OR ";
            string strCommand = "UPDATE " + tableName + " SET ";
            for (int i = 0; i < keys.Length; i++)
            {
                if (i == 0) strCommand += (keys[i] + " = @" + keys[i]);
                else strCommand += (", " + keys[i] + " = @" + keys[i]);
            }

            //append WHERE clause
            strCommand += " WHERE ";
            for (int i = 0; i < ckeys.Length; i++)
            {
                if (i == 0) strCommand += (ckeys[i] + " = @c" + ckeys[i]);
                else strCommand += (condition + ckeys[i] + " = @c" + ckeys[i]);
            }

            //set parameter for command
            OleDbCommand command = new OleDbCommand(strCommand, conn);
            foreach (string k in keys)
            {
                object value;
                valueFields.TryGetValue(k, out value);
                command.Parameters.AddWithValue("@c" + k, value);
            }
            foreach (string k in ckeys)
            {
                object value;
                conditionFields.TryGetValue(k, out value);
                command.Parameters.AddWithValue("@c" + k, value);
            }
            return command;
        }
        /// <summary>
        /// Tra ve doi tuong SqlCommand cho cau lenh delete doi voi bang duoc chi ding
        /// </summary>
        /// <param name="conn">connection</param>
        /// <param name="andCondition">dung quan he AND hay OR</param>
        /// <returns></returns>
        public OleDbCommand GetDeleteCommand(OleDbConnection conn, bool andCondition)
        {
            string[] keys = new string[conditionFields.Keys.Count];
            conditionFields.Keys.CopyTo(keys, 0);
            string condition = andCondition ? " AND " : " OR ";
            string strCommand = "DELETE FROM " + tableName + " WHERE ";
            for (int i = 0; i < keys.Length; i++)
            {
                if (i == 0) strCommand += (keys[i] + " = @" + keys[i]);
                else strCommand += (condition + keys[i] + " = @" + keys[i]);
            }
            OleDbCommand command = new OleDbCommand(strCommand, conn);
            foreach (string k in keys)
            {
                object value;
                conditionFields.TryGetValue(k, out value);
                command.Parameters.AddWithValue("@" + k, value);
            }
            return command;
        }
        //public OleDbCommand GetMultiDeleteCommand(string ids,string idCol, OleDbConnection conn)
        //{
        //    string commandText = "delete from "+tableName+" WHERE "+idCol+" IN ("+ids[0];
           
        //    return new OleDbCommand(commandText, conn); 
        //}
        /// <summary>
        /// Tra ve cau lenh INSERT voi tham so duoc truyen
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public OleDbCommand GetCreateCommand(OleDbConnection conn)
        {
            string[] keys = new string[valueFields.Keys.Count];
            valueFields.Keys.CopyTo(keys, 0);
            string strCommand = "INSERT INTO " + tableName;
            string columns = "";
            string parameters = "";
            for (int i = 0; i < keys.Length; i++)
            {
                if (i == 0)
                {
                    columns += keys[i];
                    parameters += ("@" + keys[i]);
                }
                else
                {
                    columns += (", " + keys[i]);
                    parameters += (", @" + keys[i]);
                }
            }
            strCommand += ("(" + columns + ") VALUES (" + parameters + ")");
            OleDbCommand command = new OleDbCommand(strCommand, conn);
            foreach (string k in keys)
            {
                object value;
                valueFields.TryGetValue(k, out value);
                command.Parameters.AddWithValue("@" + k, value);
            }
            return command;
        }
    }


    public class QueryCommandBuilder
    {
        ArrayList parameters;
        ArrayList statementParams;
        string strCommand = "";
        string suffix = "";
        public QueryCommandBuilder(string prefix)
        {
            parameters = new ArrayList();
            statementParams = new ArrayList();
            strCommand = prefix;
        }
        public ArrayList Parameters { get { return parameters; } }
        public void AddWithValue(string col, object value, string opr, string rel, string param,string pref,string suff)
        {
            if (String.IsNullOrEmpty(param)) param = "@" + col;
            parameters.Add(new object[] { col, value, opr, rel, param,pref,suff });
        }
        //public Dictionary<string, object> StatementParams { get; set; }
        /// <summary>
        /// Add condition parameter (for WHERE clause)
        /// </summary>
        /// <param name="col"></param>
        /// <param name="value"></param>
        /// <param name="opr"></param>
        /// <param name="rel"></param>
        /// <param name="param"></param>
        public void AddWithValue(string col, object value, string opr, string rel , string param )
        {
            if (String.IsNullOrEmpty(param)) param = "@" + col;
            parameters.Add(new object[] { col, value, opr, rel, param,"","" });
        }
        public void AddWithValue(string col, object value,string opr, string rel)
        {
            AddWithValue(col, value, opr, rel, null);
        }
        public void AddWithValue(string col, object value,string opr)
        {
            AddWithValue(col, value, opr, "AND", null);
        }
        public void AddWithValue(string col, object value)
        {
            AddWithValue(col, value, "=", "AND", null);
        }
        /// <summary>
        /// Add statement parameter
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="value"></param>
        public void AddParam(string paramName, object value)
        {
            statementParams.Add(new object[] { "#" + paramName, value });
        }
        public void AddSuffix(string suffix)
        {
            this.suffix = suffix;
        }
        /// <summary>
        /// Build and return command text with parameters (holder) in statement
        /// </summary>
        /// <returns></returns>
        public string GetCommandText()
        {
            string cmd = " ";
            foreach (object[] o in parameters)
            {
                cmd += " " + o[3].ToString() + o[5].ToString() + " " + o[0].ToString() + " " + o[2].ToString() + " " + o[4].ToString() + o[6].ToString();
            }
            return strCommand + " " + cmd + " " + suffix;
        }
        public OleDbCommand GetBuildedCommand(OleDbConnection conn)
        {
            string commandText = GetCommandText();
            foreach (object[] o in statementParams)
            {
                commandText = commandText.Replace(o[0].ToString(), o[1].ToString());
            }

            OleDbCommand cmd = new OleDbCommand(commandText, conn);
            foreach (object[] o in parameters)
            {
                cmd.Parameters.AddWithValue(o[4].ToString(), o[1]);
            }


            return cmd;
        }
    }
}
